if not exists(select * from sysobjects where id=OBJECT_ID('StudentLessonExceptionUQ'))
alter table StudentLessonException add constraint StudentLessonExceptionUQ
unique (StudentId,LessonId)
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vCertificationSatisfiedRequirement]'))
DROP VIEW [vCertificationSatisfiedRequirement]
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vGridStudentExamCreditHistory]'))
DROP VIEW [vGridStudentExamCreditHistory]
go
create view [vGridStudentExamCreditHistory] as 
SELECT     a.MemberNumber, 
		   a.StudentId, 
		   a.FirstName, 
		   a.LastName, 
		   b.Grade, 
		   c.PassGrade, 
		   c.MaxGrade, 
		   d.LessonName, 
		   d.LessonCode, 
		   p.PeriodCode, 
		   p.isOpen, 
		   p.PeriodId, 
           c.ExamDate, 
           b.ClassMemberId, 
           c.ClassId, 
           b.CandidateCode, 
           ds.DiplomaSuperTypeCode,
           p.periodDate,
           null as StudentLessonExceptionId,
           d.lessonid, -- also used in DAS periods processing
		   'EXAM' as HistoryType
FROM         Student AS a INNER JOIN
                      ClassMember AS b ON a.StudentId = b.StudentId INNER JOIN
                      Class AS c ON b.ClassId = c.ClassId INNER JOIN
                      Lesson AS d ON c.LessonId = d.LessonId INNER JOIN
                      Period AS p ON c.PeriodId = p.PeriodId INNER JOIN
                      DiplomaSuperType AS ds ON p.DiplomaSuperTypeId = ds.DiplomaSuperTypeId
union
SELECT	a.MemberNumber, 
		a.StudentId, 
		a.FirstName, 
		a.LastName, 
		null as Grade,  
		null as PassGrade, 
		null as MaxGrade,
		d.LessonName,
		d.LessonCode,
		null as PeriodCode,
		1 as isopen,
		null as PeriodId,
		b.effectiveDate as ExamDate,
		null as ClassMemberId, 
        null as ClassId, 
        null as CandidateCode,
        null as DiplomaSuperTypeCode,
        b.effectiveDate as periodDate, -- bring efective date as "perioddate", used in DAS periods processing
		b.StudentLessonExceptionId,
		b.lessonid, -- also used in DAS periods processing
		'CREDIT' as HistoryType
FROM         Student AS a INNER JOIN
                      StudentLessonException AS b ON a.StudentId = b.StudentId INNER JOIN
                      Lesson AS d ON b.LessonId = d.LessonId
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vDiplomaSuperTypeLesson]'))
DROP VIEW [vDiplomaSuperTypeLesson]
go
create view [vDiplomaSuperTypeLesson] as 
/** View used to link lessons to the diploma super type **/
SELECT     a.DiplomaSuperTypeId, cr.LessonId, 'Main' as [type]
FROM         DiplomaSuperType AS a INNER JOIN
                      DiplomaType AS d ON a.DiplomaSuperTypeId = d.DiplomaSuperTypeId INNER JOIN
                      DiplomaRequirement AS r ON d.DiplomaTypeId = r.DiplomaTypeId INNER JOIN
                      CertificationType AS ct ON r.CertificationTypeId = ct.CertificationTypeId INNER JOIN
                      CertificationRequirement AS cr ON ct.CertificationTypeId = cr.CertificationTypeId
union
SELECT     a.DiplomaSuperTypeId, cr.LessonIdAlternative, 'Alternative' as [type]
FROM         DiplomaSuperType AS a INNER JOIN
                      DiplomaType AS d ON a.DiplomaSuperTypeId = d.DiplomaSuperTypeId INNER JOIN
                      DiplomaRequirement AS r ON d.DiplomaTypeId = r.DiplomaTypeId INNER JOIN
                      CertificationType AS ct ON r.CertificationTypeId = ct.CertificationTypeId INNER JOIN
                      CertificationRequirement AS cr ON ct.CertificationTypeId = cr.CertificationTypeId
where cr.LessonIdAlternative is not null
union
SELECT     a.DiplomaSuperTypeId, cr.LessonIdAlternative1,'Alternative Other' as [type]
FROM         DiplomaSuperType AS a INNER JOIN
                      DiplomaType AS d ON a.DiplomaSuperTypeId = d.DiplomaSuperTypeId INNER JOIN
                      DiplomaRequirement AS r ON d.DiplomaTypeId = r.DiplomaTypeId INNER JOIN
                      CertificationType AS ct ON r.CertificationTypeId = ct.CertificationTypeId INNER JOIN
                      CertificationRequirement AS cr ON ct.CertificationTypeId = cr.CertificationTypeId
where cr.LessonIdAlternative1 is not null
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vCertificationLesson]'))
DROP VIEW vCertificationLesson
go
create view vCertificationLesson as
SELECT  cr.LessonId, cr.CertificationTypeId,'main' as type
FROM    CertificationRequirement cr
union
SELECT  cr.LessonIdAlternative, cr.CertificationTypeId,'alt' as type
FROM    Lesson AS ls INNER JOIN
        CertificationRequirement AS cr ON ls.LessonId = cr.LessonId 
where  cr.LessonIdAlternative is not null
union
SELECT  cr.LessonIdAlternative1, cr.CertificationTypeId,'alt1' as type
FROM    Lesson AS ls INNER JOIN
        CertificationRequirement AS cr ON ls.LessonId = cr.LessonId
where  cr.LessonIdAlternative1 is not null
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'vGridCertificationEarnedHistory'))
DROP VIEW vGridCertificationEarnedHistory
go
create view vGridCertificationEarnedHistory as
SELECT  a.CertificationSatisfiedRequirementId, 
		a.CertificationRequirementId, 
		a.SatisfiedPeriodProcessId, 
		a.CertificationEarnedId, 
		c.StudentId, 
		c.EarnedPeriodProcessId, 
        e.PeriodDate, 
        e.PeriodCode, 
        d.PeriodProcessDate, 
        f.Grade, 
        ls.LessonCode, 
        ls.LessonName, 
        cl.ExamDate, 
        'EXAM' AS HistoryType
FROM         Period AS e INNER JOIN
                      PeriodProcess AS d ON e.PeriodId = d.PeriodId INNER JOIN
                      Class AS cl INNER JOIN
                      ClassMember AS f ON cl.ClassId = f.ClassId INNER JOIN
                      Lesson AS ls ON cl.LessonId = ls.LessonId INNER JOIN
                      CertificationSatisfiedRequirement AS a INNER JOIN
                      CertificationEarned AS c ON a.CertificationEarnedId = c.CertificationEarnedId ON f.ClassMemberId = a.ClassMemberId ON 
                      d.PeriodProcessId = a.SatisfiedPeriodProcessId
WHERE   a.ClassMemberId IS NOT NULL
union
SELECT  a.CertificationSatisfiedRequirementId, 
		a.CertificationRequirementId, 
		a.SatisfiedPeriodProcessId, 
		a.CertificationEarnedId, 
		c.StudentId, 
		c.EarnedPeriodProcessId, 
		e.PeriodDate, 
        e.PeriodCode, 
        d.PeriodProcessDate, 
        NULL AS Grade, 
        ls.LessonCode, 
        ls.LessonName, 
        se.effectivedate as ExamDate, 
        'CREDIT' AS HistoryType
FROM         PeriodProcess AS d INNER JOIN
                      Period AS e ON d.PeriodId = e.PeriodId INNER JOIN
                      CertificationSatisfiedRequirement AS a INNER JOIN
                      CertificationEarned AS c ON a.CertificationEarnedId = c.CertificationEarnedId INNER JOIN
                      Lesson AS ls INNER JOIN
                      StudentLessonException AS se ON ls.LessonId = se.LessonId INNER JOIN
                      StudentLessonExceptionUsage AS eu ON se.StudentLessonExceptionId = eu.StudentLessonExceptionId ON 
                      a.StudentLessonExceptionUsageId = eu.StudentLessonExceptionUsageId ON d.PeriodProcessId = a.SatisfiedPeriodProcessId
WHERE     (a.StudentLessonExceptionUsageId IS NOT NULL)
go
if exists (select * from sysobjects where id=OBJECT_ID('StudentLessonExceptionUsage_R03'))
alter table StudentLessonExceptionUsage 
drop constraint StudentLessonExceptionUsage_R03
go
usp_dropcol 'StudentLessonExceptionUsage','ExceptionUsagePeriodId'
go
if exists(select * from sysobjects where id=object_id('[DASExpectedHolders]'))
drop table [DASExpectedHolders]
go
CREATE TABLE [DASExpectedHolders](
	[MemberNumber] [int] NOT NULL, earnedDate date
) ON [PRIMARY]
go
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4707	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(2819	,'2009-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(3974	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4711	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4487, '2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4191	,'2010-05-26')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4496	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4541	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4540	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4426	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4795	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4542	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4612	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4486	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4382	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(575	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4613	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4539	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4453	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4380	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4259	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(862	,'2009-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4548	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4214	,'2009-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4625	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4619	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4356	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4713	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(1309	,'2009-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4504	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4631	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4739	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4397	,'2010-03-16')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4406	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(2335	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4147	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4084	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4523	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(296	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(2068	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(2043	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4528	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4420	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4702	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4741	,'2012-06-05')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4357	,'2011-06-30')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4490	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4492	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4464	,'2011-03-14')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4680	,'2010-05-26')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4402	,'2010-09-03')
insert into DASExpectedHolders (memberNumber, earnedDate)  values(4542	,'2011-03-14')
--insert into DASExpectedHolders (memberNumber, earnedDate)  values('2009-06-30')
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DASMissingRequirements]') AND type in (N'U'))
DROP TABLE [DASMissingRequirements]
go
CREATE TABLE [DASMissingRequirements](
	[StudentId] [int] NOT NULL,
	LessonId [int] NOT NULL
) 
go
if not exists(select * from StudentLessonException where StudentId=2550 and LessonId=6)
begin
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 2550,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 2550,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 2550,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 2819,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4241,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4241,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4241,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4241,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4241,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4525,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4525,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6251,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6251,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6251,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6251,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6405,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6405,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6405,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6407,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6407,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6407,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6407,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6418,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6418,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6418,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6423,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6423,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6427,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6427,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6439,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6439,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6445,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6445,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6445,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6445,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6462,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6462,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6462,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6462,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6487,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6487,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6487,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6487,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6490,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6490,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6490,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6504,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6504,26,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6504,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6504,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6504,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6529,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6529,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6529,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6529,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,19,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,24,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,25,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,23,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6531,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6537,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6576,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6576,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6576,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6576,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6576,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6577,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6589,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6589,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6589,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6589,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6595,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6595,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6595,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6496,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6496,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6496,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6652,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6652,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6652,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6713,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6713,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6713,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6713,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6713,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6492,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6492,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 1203,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 1203,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 1203,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 2819,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4265,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,26,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6382,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6423,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6427,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6439,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6439,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6469,19,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6516,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6516,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6520,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6530,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6530,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6530,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6595,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,19,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6647,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,19,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,26,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6658,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 363,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 363,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 363,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 363,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 4265,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6383,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6383,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6383,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6407,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6469,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6492,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6492,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6520,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6520,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,7,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,26,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6583,19,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6656,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,19,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,24,'DAS Conversion','2009-06-01')
--INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6673,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6675,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6675,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6675,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6490,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,6,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,26,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,24,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6304,23,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 363,25,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6216,7,'DAS Conversion','2009-06-01')
INSERT INTO StudentLessonException(StudentId,LessonId,comments,EffectiveDate) values( 6216,19,'DAS Conversion','2009-06-01');
INSERT INTO StudentLessonException(LessonId,StudentId,comments,EffectiveDate) 
select  Distinct s.LessonId,6488,7, '2010-09-01' from [vDiplomaSuperTypeLesson] s
where type='Main' and DiplomaSuperTypeId=5;
end
go

